package com.gxwzxy.medic.dao.Impl;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import com.gxwzxy.medic.dao.AreaDao;
import com.gxwzxy.medic.entity.Area;
import com.gxwzxy.medic.entity.otherentity.OtherArea;
import com.gxwzxy.medic.until.Connect;

public class AreaDaoImpl implements AreaDao {

	//声明连接数据库的变量
	Connection conn = null;
	ResultSet rs = null;
	PreparedStatement ps = null;
	
	/**
	 * 根据Pid查询地区信息,与此项目无关
	 */
	@Override
	public List<OtherArea> getAreaInfoDao(String pid) {
		//声明变量
		ArrayList<OtherArea> la = null;
		String sql = null;
		try {
			//获取连接			
			conn = Connect.getConnection();
			//创建SQL命令
			sql = "select * from t_area where parentid = ?";
			//创建SQL命令对象
			ps = conn.prepareStatement(sql);
			//给占位符赋值
			ps.setString(1,pid);
			//执行sql
			rs = ps.executeQuery();
			la = new ArrayList<>();
			//遍历结果
			while(rs.next()){
				//创建实体类对象
				OtherArea a = new OtherArea(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getInt(4),rs.getInt(5));
				la.add(a);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			Connect.close(conn, ps, rs);
		}
		return la;
	}

	/**
	 * 查询行政区域个数
	 */
	@Override
	public int findAreaSize() {
		String sql = "select COUNT(*) from area";
		int i = 0;
		try {
			//调用静态方法
			rs = Connect.executeF(sql);
			if(rs.next()) {
				i = rs.getInt(1);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			Connect.close(conn, ps, rs);
		}
		return i;
	}
	
	/**
	 * 分页查询，start是开始的页数，size是一页行有多少数据
	 */
	@Override
	public List<Area> findAreaIndex(int start, int size) {
		// 声明变量
		ArrayList<Area> aa = new ArrayList<>();
		String sql = null;
		try {
			// 创建Sql语句
			sql = "select * from area limit ?,?";
			// 执行sql语句
			rs =Connect.executeF(sql, start,size);
			// 获取结果
			while (rs.next()) {
				Area a = new Area(rs.getString(1), rs.getString(2), rs.getString(3));
				aa.add(a);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Connect.close(conn, ps, rs);
		}
		// 关闭连接
		return aa;
	}

	/**
	 * 查询所有行政区域
	 */
	@Override
	public List<Area> findAllArea() {
		// 声明变量
		ArrayList<Area> aa = new ArrayList<>();
		String sql = null;
		try {
			// 创建Sql语句
			sql = "select * from area ";
			// 执行sql语句
			rs =Connect.executeF(sql);
			// 获取结果
			while (rs.next()) {
				Area a = new Area(rs.getString(1), rs.getString(2), rs.getString(3));
				aa.add(a);
			}
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			Connect.close(conn, ps, rs);
		}
		return aa;
	}

	/**
	 * 查询该行政区域是否存在下级行政区域
	 */
	@Override
	public String findNextArea(String oldareaCode, String grade) {
		//声明变量
		String sql = null;
		String areaCode = null;
		try {
			// 创建Sql语句 ,强制要求mysql只返回一条数据，这条数据是里面编号最大，但是非常损耗性能
			sql = "select * from area where areacode like concat(?,'%') and grade=? ORDER BY areacode desc limit 1";
			// 执行sql语句
			rs =Connect.executeF(sql,oldareaCode,grade);
			while (rs.next()) {
				areaCode = rs.getString("areacode");
			}
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			Connect.close(conn, ps, rs);
		}
		return areaCode;
	}

	/**
	 * 保存新行政区域
	 */
	@Override
	public boolean saveArea(String areacode, String grade, String newareaName) {
		//声明变量
		String sql = "insert into area(areacode,areaname,grade) value(?,?,?)";
		int i = Connect.executeDML(sql, areacode,newareaName,grade);
		if(i!=-1) {
			return true;
		}else {
			return false;
		}
	}

	/**
	 * 根据areaCode查找行政区域
	 */
	@Override
	public List<Area> findAreaByareaCode(String areaCode) {
		//声明变量
		ArrayList<Area> aa = new ArrayList<Area>();
		String sql = "select * from area where areacode = ?";
		try {
			rs = Connect.executeF(sql, areaCode);
			while(rs.next()) {
				Area area = new Area(rs.getString(1),rs.getString(2),rs.getString(3));
				aa.add(area);
			}
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			Connect.close(conn, ps, rs);
		}
		return aa;
	}

	/**
	 * 根据areaCode修改行政区域
	 */
	@Override
	public boolean updateArea(Area a) {
		//声明sql语句
		String sql = "update area set areaname = ?,grade = ? where areacode = ?";
		//执行方法
		int i = Connect.executeDML(sql, a.getAreaName(),a.getGrade(),a.getAreaCode());
		if(i!=-1) {
			return true;
		}else {
			return false;
		}
		
	}

	/**
	 * 根据areaCode删除行政区域
	 */
	@Override
	public boolean deleteArea(String areaCoed) {
		//声明sql语句
		String sql = "delete from area where areacode like concat(?,'%')";
		//执行方法
		int i = Connect.executeDML(sql,areaCoed);
		if(i!=-1) {
			return true;
		}else {
			return false;
		}
	}

	/**
	 * 联动
	 */
	@Override
	public List<Area> ldArea(Area a) {
		//声明变量
		ArrayList<Area> aa = new ArrayList<Area>();
		String sql = "select * from area where areacode like concat(?,'%') and grade=?";
		try {
			rs = Connect.executeF(sql, a.getAreaCode(),a.getGrade());
			while(rs.next()) {
				Area area = new Area(rs.getString(1),rs.getString(2),rs.getString(3));
				aa.add(area);
			}
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			Connect.close(conn, ps, rs);
		}
		return aa;
	}

}
